<?php

/**
 * Description of Dao
 *
 * @author ghanemel
 */
class Dao extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
        $this->load->helper('date');
    }

    public function insertDiscussion($data) {
        return $this->db->insert('discussion', $data);
    }

    public function insertTodo($data) {
        return $this->db->insert('todo', $data);
    }

    public function insertNotification($data) {
        return $this->db->insert('notification', $data);
    }

    public function insertUser($data) {
        $ret = $this->db->insert('user', $data);
        $data = Array();
        $data['s_net_id'] = '3';
        $data['user_id'] = $this->db->insert_id();
        $data['url'] = site_url('profile/of/' . $data['user_id']);
        $this->db->insert('user_social', $data);
        return $ret;
    }

    public function verifOnDb($login, $pass) {
        //$sql = "select * from user where login='$login' and password='$pass'";
        // $query = $this->db->query($sql);
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('login', $login);
        $this->db->where('password', $pass);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $result = $query->result();
            foreach ($result as $user) {
                $user->password = "********";
                if ($this->findCityById($user->city) != null)
                    $user->city = $this->findCityById($user->city)->name;

                if ($this->findCountryById($user->country))
                    $user->country = $this->findCountryById($user->country)->name;

                //log_message('info', 'birthday = ' . $user->birthday);
                $user->birthday_date = $user->birthday;
                if (isset($user->birthday)) {
                    $user->birthday = $this->getAgeFromBirthday($user->birthday) . " years";
                } else {
                    $user->birthday = "";
                }
                $this->session->set_userdata('logged_in_user', $user);
            }
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function findDiscussion($userId, $to) {
        $sql = "select * from (select * from discussion where 
            (from_user='$userId' and to_user='$to') 
            or (to_user='$userId' and from_user='$to') order by dt desc limit 0, 15) as disc 
                order by dt asc";
        //echo $sql;
        $query = $this->db->query($sql);
        $result = $query->result();

        foreach ($result as $row) {
            //Delete all notifs for the discussion :
            $this->deleteAllNotificationsForDiscussion($row->id);

            //Get user informations
            $user = $this->findUserById($row->from_user);
            $to_user = $this->findUserById($row->to_user);
            $row->from_user = $user;
            $row->to_user = $to_user;
            $dt = $row->dt;
            $row->dt_complete = $dt;
            $today = date('Y-m-d');
            if (strpos($dt, $today) !== false) {
                $row->dt = substr($dt, -8);
            }
            $yesterday = date("Y-m-d", time() - 60 * 60 * 24);
            if (strpos($dt, $yesterday) !== false) {
                $row->dt = 'Yesterday ' . substr($dt, -8);
            }
        }
        return $result;
    }

    public function findDiscussionsPlus($dt_complete, $userId, $to) {
        $dt_complete = str_replace("%20", " ", $dt_complete);
        $sql = "select * from (select * from discussion where 
            (dt<'$dt_complete') and ((from_user='$userId' and to_user='$to') 
            or (to_user='$userId' and from_user='$to')) order by dt desc limit 0, 15) as disc 
                order by dt asc";
        //echo $sql;
        $query = $this->db->query($sql);
        $result = $query->result();

        foreach ($result as $row) {
            //Delete all notifs for the discussion :
            $this->deleteAllNotificationsForDiscussion($row->id);

            //Get user informations
            $user = $this->findUserById($row->from_user);
            $to_user = $this->findUserById($row->to_user);
            $row->from_user = $user;
            $row->to_user = $to_user;
            $dt = $row->dt;
            $row->dt_complete = $dt;
            $today = date('Y-m-d');
            if (strpos($dt, $today) !== false) {
                $row->dt = substr($dt, -8);
            }
            $yesterday = date("Y-m-d", time() - 60 * 60 * 24);
            if (strpos($dt, $yesterday) !== false) {
                $row->dt = 'Yesterday ' . substr($dt, -8);
            }
        }
        return $result;
    }

    public function findUserById($id) {
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('id', $id);
        $query = $this->db->get();
        $user = $query->result();
        if (isset($user[0])) {
            $user[0]->password = "*************************";
            if ($this->findCityById($user[0]->city) != null)
                $user[0]->city = $this->findCityById($user[0]->city)->name;
            else
                $user[0]->city = null;
            if ($this->findCountryById($user[0]->country))
                $user[0]->country = $this->findCountryById($user[0]->country)->name;
            else
                $user[0]->country = null;

            $user[0]->networks = $this->findNetworksByUser($user[0]);
            //echo $user[0]->birthday;
            if (isset($user[0]->birthday) && $user[0]->birthday != null && $user[0]->birthday != "0000-00-00") {
                $user[0]->birthday_date = $user[0]->birthday;
                $user[0]->birthday = $this->getAgeFromBirthday($user[0]->birthday) . " years";
            }
            $user[0]->statut = $this->findStatus($user[0]->id);
            return $user[0];
        } else {
            return null;
        }
    }

    public function getAgeFromBirthday($birthday) {
        $date = new DateTime($birthday);
        $now = new DateTime();
        $interval = $now->diff($date);
        return $interval->y;
    }

    public function findNotifsNbr($Userid) {
        $this->db->select('count(to_user) as count');
        $this->db->from('notification');
        $this->db->where('to_user', $Userid);
        $query = $this->db->get();
        $notifUsr = $query->result();
        //echo json_encode($notifUsr);
        return $notifUsr[0]->count;
    }

    public function findNotifsNbrByUser($userId) {
        // $sql = "select from_user, count(id) from notification where user='$userId' group by from_user";
        $this->db->select('count(id) as nbr_ntfs');
        $this->db->from('notification');
        $this->db->where('to_user', $userId);
        //$this->db->where('id', "from_user");
        $this->db->group_by('from_user');
        $query = $this->db->get();
        return $query->result();
    }

    /* returns json object */

    public function findNotifs($userId, $withId) {
        $this->db->select('*');
        $this->db->from('notification');
        $this->db->where('to_user', $userId);
        $query = $this->db->get();
        $result = $query->result();
        //print_r($result);
        if (sizeOf($result) > 0) {
            foreach ($result as $row) {
                $action_id = $row->action;
                $action = $this->findActionById($action_id);
                switch ($action_id) {
                    case 1 :
                        //Supprimer la notif dont withId est l'émetteur
                        $row->object = $this->findDiscussionById($row->object_id, false);

                        if ($row->object->from_user->id == $withId) {
                            $this->deleteAllNotificationsForDiscussion($row->object_id);
//                            $action->url = "process";
//                            $dataUserNotifs = array(
//                                'id' => 'null',
//                                'user' => $userId,
//                                'notifications' => '1',
//                                'notifToDelete' => $row->id
//                            );
//                            $this->UserNotifsMoins($dataUserNotifs);
                        }
                        break;
                }
                $row->action = $action;
            }
        }
        return $result;
    }

    public function findActionById($id) {
        $this->db->select('*');
        $this->db->from('action');
        $this->db->where('id', $id);
        $query = $this->db->get();
        $result = $query->result();
        if (sizeof($result) > 0) {
            return $result[0];
        } else {
            return null;
        }
    }

    public function findDiscussionById($id, $suppNotif) {
        $this->db->select('*');
        $this->db->from('discussion');
        $this->db->where('id', $id);
        $query = $this->db->get();
        $result = $query->result();
        if (sizeOf($result) > 0) {
            //Delete notifs for this discussion as it's seen :
            if ($suppNotif) {
                $this->deleteAllNotificationsForDiscussion($id);
            }
            //Get user informations :
            $user = $this->findUserById($result[0]->from_user);
            $to_user = $this->findUserById($result[0]->to_user);
            $result[0]->from_user = $user;
            $result[0]->to_user = $to_user;
            return $result[0];
        } else {
            return null;
        }
    }

    public function findUsers($userId) {
        $this->db->select('*');
        $this->db->from('user');
        $query = $this->db->get();
        $result = $query->result();
        if (sizeOf($result) > 0) {
            foreach ($result as $v) {
                $this->db->select('count(id) as countId ');
                $this->db->from('notification');
                $this->db->where('to_user', $userId);
                $this->db->where('from_user', $v->id);
                $query = $this->db->get();
                $res = $query->result();
                $countId = $res[0]->countId;
                $v->notifsForMe = $countId;
                $v->city = $this->findCityById($v->city);
                $v->country = $this->findCountryById($v->country);
               // $v->statut = $this->findStatus($v->id);

                $v->birthday_date = $v->birthday;
                if (isset($v->birthday)) {
                    $v->birthday = $this->getAgeFromBirthday($v->birthday) . " years";
                } else {
                    $v->birthday = "";
                }

                $v->networks = $this->findNetworksByUser($v);
                $v->statut = $this->findStatus($v->id);
            }
            return $result;
        } else {
            return array();
        }
    }

    public function findCityById($id) {
        if (($id == "") or ($id == null)) {
            return null;
        }
        $this->db->select('*');
        $this->db->from('city');
        $this->db->where('id', $id);
        $queryCity = $this->db->get();
        $resultCity = $queryCity->result();

        if (isset($resultCity[0])) {
            return $resultCity[0];
        } else {
            return null;
        }
    }

    public function findCountryById($id) {
        $this->db->select('*');
        $this->db->from('country');
        $this->db->where('id', $id);
        $qCountry = $this->db->get();
        $queryCountry = $qCountry->result();
        if (isset($queryCountry[0])) {
            return $queryCountry[0];
        } else {
            return null;
        }
    }

    public function deleteAllNotificationsForDiscussion($discId) {
        $userId = $this->session->userdata('logged_in_user')->id;
        $sql = "delete from notification where to_user='$userId' and object='discussion' and object_id='$discId'";
        $this->db->query($sql);
        //$update = $this->db->affected_rows();
    }

    public function delNotifById($ntfId) {
        $sql = "delete from notification where id='$ntfId'";
        $this->db->query($sql);
        echo $this->db->affected_rows();
    }

    public function delNotifs($notifs, $userId) {
        $sql = "delete from notification where id in ($notifs) and to_user='$userId'";
        $this->db->query($sql);
        // $nbrAff = $this->db->affected_rows();
    }

    public function findTodos() {
        $this->db->select('*');
        $this->db->from('todo');
        $this->db->order_by('dt_done, dt_insertion asc');
        return $this->db->get()->result();
    }

    public function updateToDone($id) {
        $sql = "update todo set dt_done='" . date('Y-m-d H:i:s', now()) . "' where id='" . $id . "'";
        $this->db->query($sql);
        //$update = $this->db->affected_rows();
    }

    public function searchForUsers($keywords) {
        $userId = $this->session->userdata('logged_in_user')->id;
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('match(firstname,lastname) against(\'' . $keywords . '\' IN BOOLEAN MODE)');
        $result = $this->db->get()->result();
        if (sizeOf($result) > 0) {
            foreach ($result as $v) {
                $this->db->select('count(id) as countId ');
                $this->db->from('notification');
                $this->db->where('to_user', $userId);
                $this->db->where('from_user', $v->id);
                $query = $this->db->get();
                $res = $query->result();
                $countId = $res[0]->countId;
                $v->notifsForMe = $countId;
                //Get city and country :
                $v->city = $this->findCityById($v->city);
                $v->country = $this->findCountryById($v->country);
                $v->statut = $this->findStatus($v->id);
                $v->birthday_date = $v->birthday;
                $v->birthday = $this->getAgeFromBirthday($v->birthday) . " years";
                $v->networks = $this->findNetworksByUser($v);
            }
            return $result;
        } else {
            return array();
        }
    }

    public function findNetworksByUser($to_user) {
        $sql = "select us.url url, sn.id id, name, logo FROM user_social us, social_network sn where us.s_net_id=sn.id and us.user_id='$to_user->id' order by sn.id";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function goIdle() {
        if (!$this->session->userdata('logged_in_user'))
            return;
        $id = $this->session->userdata('logged_in_user')->id;
        $dt = date('Y-m-d H:i:s', now());
        $sql = "update user set statut='b-idle', dt_statut='" . $dt . "' where id='$id'";
        $this->db->query($sql);
    }

    public function goOnline() {
        if (!$this->session->userdata('logged_in_user'))
            return;
        $id = $this->session->userdata('logged_in_user')->id;
        $dt = date('Y-m-d H:i:s', now());
        $sql = "update user set statut='a-online', dt_statut='" . $dt . "' where id='$id'";
        $this->db->query($sql);
    }

    public function toOffline($id) {
        $dt = date('Y-m-d H:i:s', now());
        $sql = "update user set statut='c-offline', dt_statut='" . $dt . "' where id='$id'";
        $this->db->query($sql);
    }

    public function toIdle($id) {
        $dt = date('Y-m-d H:i:s', now());
        $sql = "update user set statut='b-idle', dt_statut='" . $dt . "' where id='$id'";
        $this->db->query($sql);
    }

//    public function verifStatut($id, $dt_statut, $statut) {
//        $d1 = new DateTime($dt_statut);
//        $d2 = new DateTime(date('Y-m-d H:i:s', now()));
//        $diffm = $d1->diff($d2)->i;
//
//        if (($diffm > 1) && ($statut == "idle")) {
//            $this->toOffline($id);
//            $statut = "c-offline";
//        }
//        return $statut;
//    }

    public function updateTof($user_id, $tof) {
        $sql = "update user set tof='$tof' where id='$user_id'";
        $this->db->query($sql);
    }

    public function updateUserProfile($user_id, $birthday, $presentation) {
        $sql = "update user set ";
        $virgule = false;
        if ($birthday != null) {
            $sql.="birthday = '$birthday' ";
            $virgule = true;
        }

        if ($presentation != null) {
            if ($virgule) {
                $sql.=",";
            }
            $sql.="presentation='$presentation'";
        }

        if ($sql != "update user set ") {
            $sql.= " where id='$user_id'";
            $this->db->query($sql);
        }
    }

    public function updateContact($id, $data) {
        $this->db->where('id', $id);
        if (!empty($data)) {
            $this->db->update('user', $data);
        }
    }

    public function findCityByName($name) {
        $name = trim($name);
        $this->db->select("id");
        $this->db->from('city');
        $this->db->where('name', $name);

        $res = $this->db->get()->result();
        if (sizeOf($res) > 0) {
            return $res[0]->id;
        } else {
            $sql = "insert into city values (null, null, '$name')";
            $this->db->query($sql);
            return $this->db->insert_id();
        }
    }

    public function findCountryByName($name) {
        $name = trim($name);
        $this->db->select("id");
        $this->db->from('country');
        $this->db->where('name', $name);

        $res = $this->db->get()->result();
        if (sizeOf($res) > 0) {
            return $res[0]->id;
        } else {
            $sql = "insert into country values (null,  '$name')";
            $this->db->query($sql);
            return $this->db->insert_id();
        }
    }

    public function addFbAndTwitter($user_id, $fb, $twitter) {

        if ($fb != null) {
            $sql = "insert into user_social values(null,'1','$user_id','$fb')";
            $this->db->query($sql);
        }

        if ($twitter != null) {
            $sql = "insert into user_social values(null,'2','$user_id','$twitter')";
            $this->db->query($sql);
        }
    }

    public function findConnections($connections) {
        $sql = "select * from user where id in (" . $connections . "0) order by statut";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result();
    }

//    function forceStatut($dt_statut, $id_user, $statut) {
//        $dt_statut = new DateTime($dt_statut);
//        $dt = date('Y-m-d H:i:s', now());
//        $now = new DateTime($dt);
//        $interval = $dt_statut->diff($now);
//        $minutes = $interval->y * 360 * 24 * 60 +
//                $interval->m * 28 * 24 * 60 +
//                $interval->d * 24 * 60 +
//                $interval->h * 60 +
//                $interval->i;
//        // echo $minutes . " :: ";
//        if ($minutes > 15) {
//            if ($statut != "c-offline")
//                $this->toOffline($id_user);
//            return "c-offline";
//        } else if ($minutes > 2) {
//            if ($statut != "b-idle" && $statut != "c-offline")
//                $this->toIdle($id_user);
//            return "b-idle";
//        }
//        return $statut;
//    }

    function findStatutsForConnectionsOf() {
        $connections = $this->session->userdata('connections');
        $sql = "select id, statut from user where id in (" . $connections . "0) order by statut";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function deleteNetworksFor($user_id) {
        $sql = "delete from user_social where user_id='$user_id'";
        $this->db->query($sql);
    }

    public function updateUserProfileAll($user_id, $dataUser) {

        /**
         * 
          $dataUser['birthday'] = $this->input->post("birthday");
          $dataUser['firstname'] = $this->input->post("firstname");
          $dataUser['lastname'] = $this->input->post("lastname");
          $dataUser['email'] = $this->input->post("email");
          $dataUser['presentation'] = $this->input->post("presentation");
          $dataUser['sexe'] = $this->input->post("sexe");
         */
        $this->db->where('id', $user_id);
        $this->db->update('user', $dataUser);
    }

    public function verifPass($user_id, $current) {
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('id', $user_id);
        $this->db->where('password', $current);
        $resultUser = $this->db->get();
        $resultUser = $resultUser->result();

        if (isset($resultUser[0])) {
            return true;
        } else {
            return false;
        }
    }

    public function deleteAccount($user_id, $reason) {
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('id', $user_id);
        $resultUser = $this->db->get();
        $resultUser = $resultUser->result();
        if (isset($resultUser[0])) {
            $user = $resultUser[0];
            $user->dt_state = date('Y-m-d H:i:s', now());
            $user->id = null;
            $this->db->insert('deleted_user', $user);
            //Deleting user :
            $this->db->where('id', $user_id);
            $this->db->delete('user');
        } else {
            return false;
        }
        return true;
    }

    public function disableAccount($user_id, $reason) {
        $data = array(
            'state' => 'D'
        );
        $this->db->where('id', $user_id);
        $this->db->update('user', $data);

        //Insert new state :
        $data_state = array(
            'id' => null,
            'user' => $user_id,
            'state' => 'D',
            'dt_state' => date('Y-m-d H:i:s', now()),
            'reason' => $reason,
        );
        $this->db->insert('user_state', $data_state);
    }

    public function findOnlineFriends($user) {
        return $this->findUsers($user->id);
    }

    public function findLastUserChatingWith() {
        $userId = $this->session->userdata('logged_in_user')->id;
        $sql = "select id, from_user, to_user from discussion where "
                . "from_user='$userId' or to_user='$userId' "
                . " order by id desc limit 1";

        $query = $this->db->query($sql);
        $result = $query->result();

        foreach ($result as $row) {
            return $row->from_user != $userId ? $row->from_user : $row->to_user;
        }

        return '';
    }

    public function forceStatus($val) {
        $id = $this->session->userdata('logged_in_user')->id;
        $sql = "update user set offline='$val' where id='$id'";
        return $this->db->query($sql);
    }

    public function findStatus($user_id) {
        $this->db->select('*');
        $this->db->from('user');
        $this->db->where('id', $user_id);
        $resultUser = $this->db->get();
        $resultUser = $resultUser->result();
        if (isset($resultUser[0])) {
            $dt_statut = $resultUser[0]->dt_statut;
            $statut = $resultUser[0]->statut;
            if ($resultUser[0]->offline == 1) {
                return "c-offline";
            } else {
                $dt_statut = new DateTime($dt_statut);
                $dt = date('Y-m-d H:i:s', now());
                $now = new DateTime($dt);
                $interval = $dt_statut->diff($now);
                $minutes = $interval->y * 360 * 24 * 60 +
                        $interval->m * 28 * 24 * 60 +
                        $interval->d * 24 * 60 +
                        $interval->h * 60 +
                        $interval->i;
                // echo $minutes . " :: ";
                if ($minutes > 15) {
                    if ($statut != "c-offline")
                        $this->toOffline($user_id);
                    return "c-offline";
                } else if ($minutes > 2) {
                    if ($statut != "b-idle" && $statut != "c-offline")
                        $this->toIdle($user_id);
                    return "b-idle";
                }
                return $statut;
            }
        } else {
            return false;
        }
    }
}

?>